/**   
 * Copyright © 2019 eSunny Info. Tech Ltd. All rights reserved.
 * 
 * @Description:TODO(功能)
 * @className OrderServiceImpl.java
 * @Package: com.erp.service 
 * @author: yj   
 * @date: 2019年2月21日 下午7:32:47 
 */
package com.erp.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.erp.dao.OrderDaoImpl;
import com.erp.po.Customers;
import com.erp.po.Employees;
import com.erp.po.Orders;
import com.erp.po.Products;
import com.erp.util.DBHelper;
import com.erp.util.Page;

/**
 * @className OrderServiceImpl.java
 * @author yj
 * @Description:TODO(功能)
 * @Date 2019年2月21日
 */
public class OrderServiceImpl implements OrderService {

	private OrderDaoImpl od;
	
	public OrderServiceImpl() {
		od = new OrderDaoImpl();
	}
	
	/* 
	 * 使用ajax 查询product数据
	 */
	@Override
	public List<Products> findProducts() {
		Connection conn = null;
		ArrayList<Products> list = new ArrayList<Products>();
		
		try {
			conn = DBHelper.getConnection();
			ResultSet set = od.selectProducts(conn);
			while (set.next()) {
				int productId = set.getInt("PRODUCTID");
				String productName = set.getString("PRODUCT_NAME");
				
				list.add(new Products(productId, productName));
			}
			return list;
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			od.close();
		}
		
		return null;
	}

	/* 
	 * 新建订单
	 */
	@Override
	public int addOrder(Orders order) {
		Connection conn = null;
		try {
			conn = DBHelper.getConnection();
			// 事务处理
			conn.setAutoCommit(false);
			// 新建订单
			int i = od.insertOrder(conn, order);
			conn.commit();
			conn.setAutoCommit(true);
			return i;
		} catch (SQLException e) {
			try {
				conn.rollback();
				conn.setAutoCommit(true);
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			
			e.printStackTrace();
		} finally {
			od.close();
		}
		return 0;
	}

	/* 
	 * 使用ajax 查询emp数据
	 */
	@Override
	public List<Employees> findEmps() {
		Connection conn = null;
		ArrayList<Employees> list = new ArrayList<Employees>();
		
		try {
			conn = DBHelper.getConnection();
			
			ResultSet set = od.selectEmps(conn);
			while (set.next()) {
				int empID = set.getInt("EMPID");
				String empName = set.getString("EMP_NAME");
				
				list.add(new Employees(empID, empName));
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			od.close();
		}
		return null;
	}

	/* 
	 * 使用ajax 查询custs数据
	 */
	@Override
	public List<Customers> findCusts() {
		Connection conn = null;
		ArrayList<Customers> list = new ArrayList<Customers>();
		
		try {
			conn = DBHelper.getConnection();
			
			ResultSet set = od.selectCusts(conn);
			while (set.next()) {
				int customerID = set.getInt("CUSTOMERID");
				String customerName = set.getString("CUSTOMER_NAME");
				
				list.add(new Customers(customerID, customerName));
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			od.close();
		}
		return null;
	}

	/* 
	 * 使用ajax 查询order数据[分页]
	 */
	@Override
	public Page<Orders> findAllOrdersByPage(Page<Orders> page) {
		Connection conn = null;
		int total = 0;
		ArrayList<Orders> list = new ArrayList<Orders>();
		
		try {
			conn = DBHelper.getConnection();
			// 查询order的总记录数
			ResultSet set2 = od.selectOrdersByTotal(conn);
			while (set2.next()) {
				total = set2.getInt(1);
			}
			
			// 获取数据
			ResultSet set = od.selectOrdersByPage(conn, page);
			while (set.next()) {
				String orderID = set.getString("orderid");
				String orderDate = set.getString("order_date");
				// int customerID = set.getInt("customerid");
				// int empID = set.getInt("empid");
				String customerName = set.getString("customer_name");
				String empName = set.getString("emp_name");
				
				list.add(new Orders(orderID, orderDate, new Customers(customerName), new Employees(empName)));
			}
		
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			od.close();
		}
		page.setList(list);
		page.setTotal(total);
		return page;
	}

	/* 
	 * 使用ajax 根据条件查询order数据[分页]
	 */
	@Override
	public Page<Orders> findOrdersByCondition(Page<Orders> page, Orders ord) {
		Connection conn = null;
		int total = 0;
		ArrayList<Orders> list = new ArrayList<Orders>();
		
		try {
			conn = DBHelper.getConnection();
			// 查询order的总记录数
			ResultSet set2 = od.selectConditionByTotal(conn, ord);
			while (set2.next()) {
				total = set2.getInt(1);
			}
			
			// 获取数据
			ResultSet set = od.selectOrdersByCondition(conn, page, ord);
			while (set.next()) {
				String orderID = set.getString("orderid");
				String orderDate = set.getString("order_date");
				// int customerID = set.getInt("customerid");
				// int empID = set.getInt("empid");
				String customerName = set.getString("customer_name");
				String empName = set.getString("emp_name");
				
				list.add(new Orders(orderID, orderDate, new Customers(customerName), new Employees(empName)));
			}
		
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			od.close();
		}
		page.setList(list);
		page.setTotal(total);
		return page;
	}

	/* 
	 * 根据 orderId 删除订单
	 */
	@Override
	public boolean removeOrderById(String orderId) {
		Connection conn = null;
		
		try {
			conn = DBHelper.getConnection();
			
			//开启事务
			conn.setAutoCommit(false);
			od.deleteOrderByOrderId(conn, orderId);
			conn.commit();
			conn.setAutoCommit(true);
			
			return true;
		} catch (SQLException e) {
			try {
				conn.rollback();
				conn.setAutoCommit(true);
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			od.close();
		}
		return false;
	}

}
